﻿alter table bs_car_record add car_class_name nvarchar(100)
go
update bs_car_record   set car_class_name = bs_car_class.CAR_NOTE from bs_car_record ,BS_CAR_CLASS where
bs_car_record.CAR_CLASS=BS_CAR_CLASS.CAR_CLASS
go
alter table bs_car_record add car_sort varchar(20)
go
update bs_car_record   set car_sort = bs_car_class.CAR_SORT from bs_car_record ,BS_CAR_CLASS where
bs_car_record.CAR_CLASS=BS_CAR_CLASS.CAR_CLASS
go
update DEPT set del_flag='F' where del_flag is null
go
update DEPT_CHILD set is_use='T' where is_use is null
go

CREATE TABLE [dbo].[sys_dept](
	[deptcode] [nvarchar](20) NOT NULL,
	[super_dept] [nvarchar](20) NULL,
	[deptname] [nvarchar](50) NULL,
	[use_flag] [nvarchar](1) NULL,
	[dept_kind] [nvarchar](10) NULL,
	[cqep_dept] [nvarchar](10) NULL,
	[short_name] [nvarchar](50) NULL,
 CONSTRAINT [PK_sys_dept] PRIMARY KEY CLUSTERED 
(
	[deptcode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
go
INSERT INTO sys_dept
           ([deptcode]
           ,[super_dept]
           ,[deptname]
           ,[use_flag]
           ,[dept_kind]
           ,[cqep_dept]
           ,[short_name])
select dept_code,null,DEPT_NAME,case ISNULL(del_flag,'F') when 'T' then 'F' else 'T' end,dept_kind,cqep_dept,short_name
 from DEPT
go
INSERT INTO sys_dept
           ([deptcode]
           ,[super_dept]
           ,[deptname]
           ,[use_flag]
           ,[dept_kind]
           ,[cqep_dept]
           ,[short_name])
select 'C'+CHILD_DEPT,UP_DEPT,DEPT_NAME,ISNULL(is_use,'T'),null,null,null
 from DEPT_CHILD
 go
 delete from sys_dept where deptname is null or deptname=''
 go
 alter table BS_CAR_RECORD ALTER COLUMN  child_dept  nvarchar(20)
 go
 update BS_CAR_RECORD set CHILD_DEPT='C'+CHILD_DEPT
where CHILD_DEPT is not null and CHILD_DEPT <>''
go
update BS_CAR_RECORD set CHILD_DEPT=DEPT_CODE
where CHILD_DEPT is  null or CHILD_DEPT =''
go
CREATE NONCLUSTERED INDEX [ix_car_dept] ON [dbo].[BS_CAR_RECORD] 
(
	[CHILD_DEPT] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
alter table USER_GROUP add is_default nvarchar(1) default 'F'
go
update USER_GROUP set is_default='F'
go

drop table sys_group_purview
go
CREATE TABLE [dbo].[sys_group_purview](
	[group_id] [varchar](10) NOT NULL,
	[purview_id] [varchar](20) NOT NULL,
 CONSTRAINT [PK_sys_group_purview] PRIMARY KEY CLUSTERED 
(
	[group_id] ASC,
	[purview_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
go
drop table sys_user_purview
go
CREATE TABLE [dbo].[sys_user_purview](
	[userid] [varchar](20) NOT NULL,
	[purview_id] [varchar](20) NOT NULL,
 CONSTRAINT [PK_sys_user_purview] PRIMARY KEY CLUSTERED 
(
	[userid] ASC,
	[purview_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
go

alter view [dbo].[v_user_purview] as
select userid,purview_id,psource
from (
select userid,purview_id,psource,ROW_NUMBER()over(partition by  userid,purview_id order by psource desc) Rank1  from 
(
select a.oper_code as userid,b.purview_id as purview_id,2 as psource
 from GROUP_Man a,sys_group_purview b
where a.group_code=b.group_id
union
select userid,purview_id,1 from dbo.sys_user_purview c
where not exists(select 1 from GROUP_Man a,sys_group_purview b
where a.group_code=b.group_id and a.oper_code=c.userid and b.purview_id=c.purview_id)
union
select c.account,b.purview_id,3 from USER_GROUP a,sys_group_purview b,v_all_users c
where a.GROUP_CODE=b.group_id and a.is_default='T'
) as a
) as b where rank1=1
GO


delete from bs_man_childdept where userid =''
go
delete from bs_man_dept where man_code =''
go
 update bs_man_childdept set CHILD_DEPT='C'+CHILD_DEPT
 go
 insert into BS_MAN_DEPT
select * from dbo.bs_man_childdept b
go
alter table carcost drop column sum_fee
go
ALTER TABLE CarCost ADD
	sum_fee  AS (isnull([zs_fee],0) + isnull([cl_fee],0) + isnull([lq_fee],0) + isnull([xc_fee],0) + isnull([tc_fee],0) + isnull([ns_fee],0) + isnull([lq_year],0) + isnull([lq_month],0) + isnull([road_fee],0) + isnull([oil_fee],0) + isnull([other_fee],0))
GO

update BS_CAR_RECORD set CHILD_DEPT=DEPT_code where CHILD_DEPT not in (select deptcode from sys_dept where IS_USE='T')
go
 update BS_CAR_RECORD set CHILD_DEPT=DEPT_CODE
 where CAR_NO in(
 select car_no from BS_CAR_RECORD a,sys_dept b
 where a.CHILD_DEPT = b.deptcode
 and a.DEPT_CODE <> b.super_dept
 )
 go
  alter table bs_emp ALTER COLUMN  child_dept  nvarchar(20)
 go
 update bs_emp set CHILD_DEPT='C'+CHILD_DEPT
where CHILD_DEPT is not null and CHILD_DEPT <>''
go
update bs_emp set CHILD_DEPT=DEPT_CODE
where CHILD_DEPT is  null or CHILD_DEPT =''
go
 update [BS_CAR_SEND] set dept2='C'+ltrim(dept2)
where dept2 is not null and dept2 <>''
and len(dept2)=10 and dept2 like '%N'
go
update [BS_CAR_SEND] set dept2=DEPT_CODE
where dept2 is  null or dept2 =''
go
alter table bs_files add filename2 nvarchar(255)
go
update bs_files set filename2=FileName
go
alter table SEC_MONTH_REPORT add ins_date datetime default getdate()
go
insert into sys_user_purview(userid,purview_id)
select distinct account,'check_car_send' from v_all_users  where priv like '%D%'
and not exists(select 1 from sys_user_purview a
where a.purview_id='check_car_send' and a.userid=v_all_users.account)
go
insert into sys_user_purview(userid,purview_id)
select account,'check_car_delete' from v_all_users where priv like '%E%'
go
CREATE TABLE dbo.work_dynamic
	(
	id int NOT NULL IDENTITY (1, 1),
	title nvarchar(255) NOT NULL,
	image_url nvarchar(255) NOT NULL,
	html_content text NOT NULL,
	operator nvarchar(50) NOT NULL,
	data_time datetime NOT NULL,
	is_use bit
	)  ON [PRIMARY]
	 TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.work_dynamic ADD CONSTRAINT
	PK_Table_1 PRIMARY KEY CLUSTERED 
	(
	id
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
alter table work_dynamic add title2 varchar(255)
go
drop table user_view_response
go
CREATE TABLE dbo.user_view_response
	(
	table_name nvarchar(50) NOT NULL,
	table_id int NOT NULL,
	opercode nvarchar(50) NOT NULL,
	response_note nvarchar(500) NOT NULL,response_date datetime
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.user_view_response ADD CONSTRAINT
	PK_user_view_response_1 PRIMARY KEY CLUSTERED 
	(
	table_name,
	table_id,
	opercode
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

drop table report_view_response
go
CREATE TABLE dbo.report_view_response
	(
	table_name nvarchar(50) NOT NULL,
	table_id nvarchar(50) NOT NULL,
	opercode nvarchar(50) NOT NULL,
	response_note nvarchar(500) NOT NULL,response_date datetime
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.report_view_response ADD CONSTRAINT
	PK_report_view_response_1 PRIMARY KEY CLUSTERED 
	(
	table_name,
	table_id,
	opercode
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER view [dbo].[v_all_users] as 
select a.account,a.name,a.CN,b.deptname,d.deptname corpname,a.password,c.pwd oldpass,a.loginenable,'门户帐号' user_source 
,isnull(c.IS_SUPER,'F') is_super,c.priv ,b.deptcode,a.orgcode corpcode,
(select top 1 deptcode from sys_dept e where a.orgcode=e.deptcode) ori_dept
from dbo.sys_cqep_user a 
left join dbo.sys_cqep_dept b
on a.deptcode = b.deptcode
left join dbo.sys_cqep_dept d
on a.orgcode=d.deptcode
left join work_man c
 on a.account=c.OPER_CODE
union all
select a.OPER_CODE,a.OPER_NAME,a.OPER_CODE,b.DEPT_NAME,b.DEPT_NAME,a.PWD,a.PWD,'1','原车管系统帐号'
,isnull(a.IS_SUPER,'F') is_super,a.priv,b.dept_code ,b.dept_code ,b.dept_code 
 from WORK_MAN a,DEPT b where a.DEPT=b.DEPT_CODE
and not exists(select 1 from sys_cqep_user c where c.account=a.oper_code)



GO
alter table SEND_MSG_LOG add response_note nvarchar(500)
go
alter table SEND_MSG_LOG add response_date datetime
go
delete from report_view_response
go
insert into report_view_response 
select 'report',a.xh,b.account,'系统自动-已阅',GETDATE() from SEC_MONTH_REPORT a,v_all_users b
go

delete from user_view_response
go
insert into user_view_response 
select 'file',a.fileid,b.account,'系统自动-已阅',GETDATE() from bs_files a,v_all_users b
go

CREATE NONCLUSTERED INDEX [IX_SEND_MSG_LOG] ON [dbo].[SEND_MSG_LOG] 
(
	[MAN_CODE] ASC,
	[IS_VIEW] ASC
)
go

create view [dbo].[v_sys_dept_all]
as
select a.deptcode,a.super_dept,a.cqep_dept,a.dept_kind,a.deptname,a.short_name,
case  isnull(b.use_flag,'T') when 'F' then 'F' else a.use_flag end use_flag ,
isnull(b.deptname,a.deptname) super_dept_name,isnull(b.short_name,a.short_name) super_dept_short_name
 from sys_dept a left join sys_dept b
on a.super_dept = b.deptcode
go
alter table SEC_DRV_STUDY add dept_code nvarchar(50)
go
update SEC_DRV_STUDY set dept_code =(select DEPT_CODE from BS_EMP where SEC_DRV_STUDY.MAN_CODE = BS_EMP.MAN_CODE)
go
CREATE TABLE [dbo].[warn_info](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[title] [nvarchar](50) NOT NULL,
	[short_content] [nvarchar](500) NOT NULL,
	[warn_content] text NOT NULL,
	[data_time] [datetime] NOT NULL,
	[operator] [nvarchar](50) NOT NULL,
	[beg_time] [date] NOT NULL,
	[end_time] [date] NOT NULL,
 CONSTRAINT [PK_warn_info] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
go
alter table warn_info add is_float bit
go

update BS_CAR_RECORD set CAR_USE='03'
where isnull(CAR_USE,'04') in ('04','05','06')
go
update BS_CODE set BASE_NAME='集体'
where BASE_KIND='C' and BASE_CODE='8'
go
update BS_CODE set BASE_NAME='其他'
where BASE_KIND='C' and BASE_CODE='888'
go

delete from BS_CODE where BASE_KIND='C' and BASE_CODE='9'
go
update BS_CAR_RECORD set ASSET_DEPT='888'
where ASSET_DEPT ='9'
go
alter table warn_info add file_url nvarchar(255)
go

alter table bs_files add file_type varchar(10) default 'FILE'
go
update bs_files set file_type ='FILE'
go
alter table work_dynamic
add image_url2 varchar(200)
go
alter table work_dynamic
add image_url3 varchar(200)
go

CREATE TABLE [dbo].[slogan_info](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[note] [nvarchar](255) NULL,
	[is_use] [bit] NULL,
 CONSTRAINT [PK_slogan_info] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[contract_info](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[dept_name] [nvarchar](100) NOT NULL,
	[man_name] [nvarchar](100) NOT NULL,
	[mobile] [nvarchar](50) NULL,
	[office] [nvarchar](50) NULL,
	[email] [nvarchar](50) NULL,
	[address] [nvarchar](100) NULL,
	[post_code] [nvarchar](20) NULL,
	[data_time] [datetime] NULL,
	oper_code nvarchar(50),
 CONSTRAINT [PK_contract_info] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


